#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
reload(sys)
sys.setdefaultencoding( "utf-8" )

import sys
from sqlalchemy.engine import create_engine
import re
import datetime
import csv
import random

cache = {}
connection = None

def main():
 
    global connection
    
    # Open source
    engine = create_engine("sqlite:///member.sqlite")
    connection = engine.connect()
    
    # Cleanup
    #.read member-structure.sql
    connection.execute("DELETE FROM member")

    # Import facts and dimension data
    import_member()

def save_object(table, row):
    
    if (table in cache):
        if (row["id"] in cache[table]):
            return row["id"]
    else:
        cache[table] = {}
    
    keys = row.keys()
    sql = "INSERT INTO " + table + " ("
    sql = sql + ", ".join(keys)
    sql = sql + ") VALUES ("
    sql = sql + ", ".join([ ("'" + str(row[key]) + "'") for key in keys])
    sql = sql + ")"
    
    print "Inserting - Table: %-14s Id: %s" % (table, row["id"])
    #print sql
    
    connection.execute(sql)
    cache[table][row["id"]] = row      
                              
    return row["id"]

def insert_member(fact):
    return save_object ("member", fact)

def import_member():
    
    count = 0
    header = None
    with open('member.csv', 'rb') as f:
        reader = csv.reader(f)
        for row in reader:
            
            count = count + 1
            fact = { "id" : count }

            if (header == None):
                header = row
                continue

    #"ReportTime", "partner", "total", "increase", "loginmember", "buyers", "logintimes",
    #"activerate", "buymentrate", "conversionrate"]:
            arow = {}
            for header_index in range (0, len(header)):
                arow[header[header_index]] = row[header_index]

            fact["ReportTime"] = arow["ReportTime"]
            fact["partner_id"] = arow["partner_id"]
            fact["total"] = int(float(arow[u"total"]))
            fact["increase"] = int(float(arow["increase"]))
            fact["loginmember"] = int(float(arow["loginmember"]))
            fact["buyers"] = int(float(arow["buyers"]))
            fact["logintimes"] = int(float(arow["logintimes"]))
            fact["activerate"] = arow["activerate"]
            fact["buymentrate"] = arow["buymentrate"]
            fact["conversionrate"] = arow["conversionrate"]

            insert_member(fact)
            
    print "Imported %d facts " % (count)

if __name__ == "__main__":
    main()

